﻿using Microsoft.AspNetCore.Http;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using 医院预约Web.Models;

namespace 医院预约Web.Models//分区策略
{
    public class DataBase
    {
        public string ConnectionString { get; set; }//连接字符串，在appsettings.json中填写

        public DataBase(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        public MySqlConnection GetConnection()
        {
            return new MySqlConnection(ConnectionString);
        }

        public List<Classdbo> GetClassdbos()//获取所有科室
        {
            List<Classdbo> list = new List<Classdbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM `starplatinum`.`科室` ORDER BY `科室代号` ASC  LIMIT 0,50;", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        list.Add(new Classdbo()
                        {
                            classname = reader.GetString("科室"),
                            classnum = reader.GetInt32("科室代号"),
                            classplace=reader.GetString("科室位置")
                        });
                    }
                }
            }
            return list;
        }

        public List<Classdbo> GetNoFreezeMessage()//获取解冻请求
        {
            List<Classdbo> list = new List<Classdbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM `starplatinum`.`解冻请求` ;", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        list.Add(new Classdbo()
                        {
                             classname = reader.GetString("id")
                        });
                    }
                }

            }
            return list;
        }
        public List<Classdbo> GetWeekdboUpdateMessage()//获取请求
        {
            List<Classdbo> list = new List<Classdbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM `starplatinum`.`排班请求` ;", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        list.Add(new Classdbo()
                        {
                            classname = reader.GetString("id")
                        });
                    }
                }

            }
            return list;
        }
        public List<Mydbo> GetMylist(string account)
        {
            List<Mydbo> mylist = new List<Mydbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT `订单表`.`订单创建时间`,`订单表`.`预约时间`,`科室`.`科室位置`,`科室`.`科室`,`医生表`.`DoctorName`,`订单表`.`具体时间` ,`订单表`.`医嘱` ,`订单表`.`患者姓名`,`订单表`.`患者年龄`,`订单表`.`订单状态`,`医生表`.`ID`FROM `医生表`,`科室`,`订单表`  WHERE  `科室`.`科室代号`=`订单表`. `科室代号` and `医生表`.`ID`=`订单表`.`医生ID` and`订单表`.`患者账户` ='" + account+"';", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        mylist.Add(new Mydbo()
                        {
                            time = reader.GetInt32("具体时间"),
                            createtime = reader.GetString("订单创建时间"),
                            date = reader.GetString("预约时间"),
                            classplace = reader.GetString("科室位置"),
                            classname = reader.GetString("科室"),
                            doctorname = reader.GetString("DoctorName"),
                            username = reader.GetString("患者姓名"),
                            userage = reader.GetInt32("患者年龄"),
                            status = reader.GetInt32("订单状态"),
                            id = reader.GetInt32("ID"),
                            tell = reader.GetString("医嘱")
                        });
                    }
                }
            }
         return mylist;
        }

        public int GetMynum(string account)//account获取订单总数
        {
            using (MySqlConnection connection = GetConnection())
            {
                Doctordbo doctordbo = new Doctordbo();
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT COUNT(*) FROM `starplatinum`.`订单表` WHERE `患者账户` ='"+account+"';" , connection);
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    return reader.GetInt16("COUNT(*)");
                }
                return 0;
            }
        }

        public List<Doctordbo> GetDoctordbo(int type)//获取指定科室医生
        {
            List<Doctordbo> list = new List<Doctordbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM `starplatinum`.`医生表`WHERE `ClassNumber`="+type+" ORDER BY `DoctorType` ASC  LIMIT 0,50;", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        list.Add(new Doctordbo()
                        {
                            id=reader.GetInt32("ID"),
                            doctorname = reader.GetString("DoctorName"),
                            classnumber = reader.GetInt32("ClassNumber"),
                            doctortype = reader.GetInt32("DoctorType"),
                            doctorskill=reader.GetString("DoctorSkill")
                        });
                    }
                }

            }
            return list;
        }
        public List<Doctordbo> GetDoctor(int id)//获取指定科室医生
        {
            List<Doctordbo> list = new List<Doctordbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM `starplatinum`.`医生表`WHERE `ID`=" + id+ " ;", connection);
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        list.Add(new Doctordbo()
                        {
                            id = reader.GetInt32("ID"),
                            doctorname = reader.GetString("DoctorName"),
                            classnumber = reader.GetInt32("ClassNumber"),
                            doctortype = reader.GetInt32("DoctorType"),
                            doctorskill = reader.GetString("DoctorSkill")
                        });
                    }
                }

            }
            return list;
        }
        public List<Appointmentlist> GetDoctorAppointment(string id,string date)
        {
            List<Appointmentlist> list = new List<Appointmentlist>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM `starplatinum`.`订单表` WHERE `医生ID` ="+id+" AND `预约时间` ='"+date+ "' AND `订单状态` !=4 AND `订单状态` !=2  ORDER BY `订单创建时间` ASC  LIMIT 0,50;", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { 
                        list.Add(new Appointmentlist()
                        {
                            aid=reader.GetInt32("医生ID"),
                            createtime =reader.GetString("订单创建时间"),
                            name=reader.GetString("患者姓名"),
                            age=reader.GetInt32("患者年龄")
                         });
                    }
                }

            }
            return list;
        }

        public bool ConfirmAppointment(string createtime, string input)//驗證賬戶
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {  //打开MySQL
                    connection.Open();
                    MySqlCommand command = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`=2,`医嘱`='"+input+"' where `订单创建时间`='"+createtime+"';", connection);
                    command.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
        

        public List<Doctordbo> GetAllDoctordbo()
        {
            List<Doctordbo> list = new List<Doctordbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM `starplatinum`.`医生表`;", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        list.Add(new Doctordbo()
                        {
                            id = reader.GetInt32("ID"),
                            doctorname = reader.GetString("DoctorName"),
                            classnumber = reader.GetInt32("ClassNumber"),
                            doctortype = reader.GetInt32("DoctorType"),
                            doctorskill = reader.GetString("DoctorSkill")
                        });
                    }
                }

            }
            return list;
        }
        public bool countappointment(string account)
        {
            using (MySqlConnection msconnection = GetConnection())
            {
                msconnection.Open();
                MySqlCommand checkcommand = new MySqlCommand("SELECT COUNT(*) FROM `starplatinum`.`订单表` WHERE `患者账户` ='user' AND `医生ID` =1001 AND `订单状态` =1;", msconnection);
                using (MySqlDataReader reader2 = checkcommand.ExecuteReader())
                {
                    while (reader2.Read())
                    {
                        if (Convert.ToInt16(reader2[0])==0) return false;
                    }
                }
            }
            return true;
        }
        public bool Appointmentornot(int id, string account)
        {
            using (MySqlConnection msconnection = GetConnection())
            {
                msconnection.Open();
                MySqlCommand fristcommand = new MySqlCommand(" SELECT COUNT(医生ID) FROM `starplatinum`.`订单表` WHERE `医生ID` = " + id + "  AND `患者账户` = '" + account + "'AND `订单状态` =0;", msconnection); //读取排班表数据 
                using (MySqlDataReader reader = fristcommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.GetInt32("COUNT(医生ID)") == 0) return true;
                    }
                }
            }return false;
        }
        public string GetWeekdbo(int id, int week,string dateTime,int i)//id=xxx ,星期=x 是否排班？是否约满？
        {//这是输入医生ID，指定时间（2018/11/22上午），输出true/flase。查询是否可预约的函数
            Weekdbo weekdboclass = new Weekdbo();
            using (MySqlConnection msconnection = GetConnection())
            {
                msconnection.Open();
              
                        MySqlCommand mscommand = new MySqlCommand("SELECT * FROM `starplatinum`.`排班表` WHERE `ID` = " + id + ";", msconnection); //读取排班表数据 
                using (MySqlDataReader reader = mscommand.ExecuteReader())
                {
                    while (reader.Read())//读取周一到周五排班（0：没排班，1：排班）
                    {
                        weekdboclass.monday = reader.GetInt32("1");
                        weekdboclass.tuesday = reader.GetInt32("2");
                        weekdboclass.wednesday = reader.GetInt32("3");
                        weekdboclass.thursday = reader.GetInt32("4");
                        weekdboclass.friday = reader.GetInt32("5");
                        weekdboclass.saturday = reader.GetInt32("6");
                        weekdboclass.sunday = reader.GetInt32("7");
                    }
                }
                if (weekdboclass.monday == null) return "0000000";
                switch (week)//（没排班就返回）
                {
                    case 1:
                        if (weekdboclass.monday == 0)
                            return "0000000"; break;
                    case 2:
                        if (weekdboclass.tuesday == 0)
                            return "0000000"; break;
                    case 3:
                        if (weekdboclass.wednesday == 0)
                            return "0000000"; break;
                    case 4:
                        if (weekdboclass.thursday == 0)
                            return "0000000"; break;
                    case 5:
                        if (weekdboclass.friday == 0)
                            return "0000000"; break;
                    case 6:
                        if (weekdboclass.saturday == 0)
                            return "0000000"; break;
                    case 7:
                        if (weekdboclass.sunday == 0)
                            return "0000000"; break;
                    case 8:
                        if (weekdboclass.monday == 0)
                            return "0000000"; break;
                    case 9:
                        if (weekdboclass.tuesday == 0)
                            return "0000000"; break;
                    case 10:
                        if (weekdboclass.wednesday == 0)
                            return "0000000"; break;
                    case 11:
                        if (weekdboclass.thursday == 0)
                            return "0000000"; break;
                    case 12:
                        if (weekdboclass.friday == 0)
                            return "0000000"; break;
                    case 13:
                        if (weekdboclass.saturday == 0)
                            return "0000000"; break;
                    case 14:
                        if (weekdboclass.sunday == 0)
                            return "0000000"; break;
                }//检查排班表               parameter
                weekdboclass.hao = "";
                MySqlCommand checkcommand = new MySqlCommand("SELECT COUNT(医生ID) FROM `starplatinum`.`订单表` WHERE `医生ID` =" + id + " AND `预约时间` = '" + dateTime + "'  AND `具体时间` =1;", msconnection);
                using (MySqlDataReader reader2 = checkcommand.ExecuteReader())//
                {
                    while (reader2.Read())
                    {
                        if (i == 1)
                        {
                            if (Convert.ToInt16(reader2[0]) < 4) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                        else
                        {
                            if (Convert.ToInt16(reader2[0]) < 2) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                    }
                }
                MySqlCommand checkcommand2 = new MySqlCommand("SELECT COUNT(医生ID) FROM `starplatinum`.`订单表` WHERE `医生ID` =" + id + " AND `预约时间` = '" + dateTime + "'  AND `具体时间` =2;", msconnection);
                using (MySqlDataReader reader3 = checkcommand2.ExecuteReader())//
                {
                    while (reader3.Read())
                    {
                        if (i == 1)
                        {
                            if (Convert.ToInt16(reader3[0]) < 4) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                        else
                        {
                            if (Convert.ToInt16(reader3[0]) < 2) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                    }
                }
                MySqlCommand checkcommand3 = new MySqlCommand("SELECT COUNT(医生ID) FROM `starplatinum`.`订单表` WHERE `医生ID` =" + id + " AND `预约时间` = '" + dateTime + "'  AND `具体时间` =3;", msconnection);
                using (MySqlDataReader reader4 = checkcommand3.ExecuteReader())//
                {
                    while (reader4.Read())
                    {
                        if (i == 1)
                        {
                            if (Convert.ToInt16(reader4[0]) < 4) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                        else
                        {
                            if (Convert.ToInt16(reader4[0]) < 2) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                    }
                }
                MySqlCommand checkcommand4 = new MySqlCommand("SELECT COUNT(医生ID) FROM `starplatinum`.`订单表` WHERE `医生ID` =" + id + " AND `预约时间` = '" + dateTime + "'  AND `具体时间` =4;", msconnection);
                using (MySqlDataReader reader5 = checkcommand4.ExecuteReader())//
                {
                    while (reader5.Read())
                    {
                        if (i == 1)
                        {
                            if (Convert.ToInt16(reader5[0]) < 4) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                        else
                        {
                            if (Convert.ToInt16(reader5[0]) < 2) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                    }
                }
                MySqlCommand checkcommand5 = new MySqlCommand("SELECT COUNT(医生ID) FROM `starplatinum`.`订单表` WHERE `医生ID` =" + id + " AND `预约时间` = '" + dateTime + "'  AND `具体时间` =5;", msconnection);
                using (MySqlDataReader reader6 = checkcommand5.ExecuteReader())//
                {
                    while (reader6.Read())
                    {
                        if (i == 1)
                        {
                            if (Convert.ToInt16(reader6[0]) < 4) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                        else
                        {
                            if (Convert.ToInt16(reader6[0]) < 2) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                    }
                }
                MySqlCommand checkcommand6 = new MySqlCommand("SELECT COUNT(医生ID) FROM `starplatinum`.`订单表` WHERE `医生ID` =" + id + " AND `预约时间` = '" + dateTime + "'  AND `具体时间` =6;", msconnection);
                using (MySqlDataReader reader7 = checkcommand6.ExecuteReader())//
                {
                    while (reader7.Read())
                    {
                        if (i == 1)
                        {
                            if (Convert.ToInt16(reader7[0]) < 4) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                        else
                        {
                            if (Convert.ToInt16(reader7[0]) < 2) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                    }
                }
                MySqlCommand checkcommand7 = new MySqlCommand("SELECT COUNT(医生ID) FROM `starplatinum`.`订单表` WHERE `医生ID` =" + id + " AND `预约时间` = '" + dateTime + "'  AND `具体时间` =7;", msconnection);
                using (MySqlDataReader reader8 = checkcommand7.ExecuteReader())//
                {
                    while (reader8.Read())
                    {
                        if (i == 1)
                        {
                            if (Convert.ToInt16(reader8[0]) < 4) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                        else
                        {
                            if (Convert.ToInt16(reader8[0]) < 2) weekdboclass.hao += "1";
                            else weekdboclass.hao += "0";
                        }
                    }
                }

            }
            return weekdboclass.hao;
        }

        public string GetDoctorName(int id)//id获取医生名字
        { 
            using (MySqlConnection connection = GetConnection())
            {
                Doctordbo doctordbo = new Doctordbo();
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT DoctorName  FROM `starplatinum`.`医生表` WHERE `ID` =" + id+";", connection);
                MySqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                       doctordbo.doctorname=reader["DoctorName"].ToString();
                    }
                    return doctordbo.doctorname;
          }   
        }

        public List<Account_password> GetAllAccount()//该方法获取所有account&password
        {
                List<Account_password> list = new List<Account_password>();
                using (MySqlConnection msconnection = GetConnection())
                { //打开MySQL
                    msconnection.Open(); //查找数据库中的表 
                    MySqlCommand mscommand = new MySqlCommand("select * from account_password", msconnection); //数据读取 
                    using (MySqlDataReader reader = mscommand.ExecuteReader())
                    {
                        while (reader.Read())
                        { //读取数据 
                            list.Add(new Account_password()
                            {
                                account = reader.GetString("Account"),
                                password = reader.GetString("Password"),
                                type = reader.GetString("Type"),
                                createtime=reader.GetMySqlDateTime("CreateTime").ToString(),
                                message=reader.GetString("Message")
                            });
                        }
                    }
                }
                return list;
        }

        public bool isaccountright(string account,string password)//驗證賬戶
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {  //打开MySQL
                    connection.Open();
                    MySqlCommand command = new MySqlCommand("select Password from account_password where Account = '" + account + "'", connection);
                    MySqlDataReader mySqlDataReader = command.ExecuteReader();
                    while (mySqlDataReader.Read())
                    {
                        string pswstr = mySqlDataReader["Password"].ToString();
                        mySqlDataReader.Close();
                        if (pswstr == password)
                            return true;
                        else return false;
                    }
                    if (mySqlDataReader.Read())
                        return true;                  
                }
            }
            catch (Exception)
            {
                return false;
            }
            return false;
        }

        public string getviplevel(string account)//判定用户/管理员（TLC
        {
                using (MySqlConnection connection = GetConnection())
                {  //打开MySQL
                    connection.Open();
                    MySqlCommand command = new MySqlCommand("select Type from account_password where Account = '" + account + "'", connection);
                    MySqlDataReader mySqlDataReader = command.ExecuteReader();
                mySqlDataReader.Read();                   
                     string type1 = mySqlDataReader[0].ToString();             
                mySqlDataReader.Close();
                    return type1;
                }            
        }

        public bool dbregister(string account, string password,DateTime datetime,string message)//注册
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand registercommand = new MySqlCommand("insert `starplatinum`.`account_password`(`Account`,`Password`,`Type`,`CreateTime`,`Message`) values('" + account + "','" + password + "','1','" + datetime + "','" + message + "');", connection);
                    registercommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch(Exception)
            {
                return false;
            }
        }
       public bool NewClass(string classname, string classplace)
        {
            try
            {
                Classdbo classdbo = new Classdbo();//只是用来传递COUNT(*)的值
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand qurecommand = new MySqlCommand("SELECT COUNT(*) FROM `starplatinum`.`科室` ;", connection);
                    MySqlDataReader qurereader = qurecommand.ExecuteReader();
                    while (qurereader.Read())
                    {
                        classdbo.classnum = qurereader.GetInt16("COUNT(*)");
                    }
                    connection.Close();
                    int classnum = classdbo.classnum + 1;
                    connection.Open();
                    MySqlCommand firstcommand = new MySqlCommand("insert `starplatinum`.`科室`(`科室代号`,`科室`,`科室位置`) values(" + classnum + ",'" + classname + "','" + classplace + "');", connection);
                    firstcommand.ExecuteNonQuery();
                }
                return true;
            }catch(Exception)
            { return false;}
                
        }
       public int newDoctor(string DoctorName, int ClassNumber, string DoctorSkill,  string Password, int DoctorType, DateTime CreateTime, string Message,int d1,int d2,int d3,int d4,int d5,int d6,int d7)
        {
            Classdbo classdbo = new Classdbo();//只是用来传递COUNT(*)_ID的值
           
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand qurecommand =new MySqlCommand("SELECT COUNT(*) FROM `starplatinum`.`医生表` WHERE `ClassNumber` ="+ClassNumber+";", connection);
                    MySqlDataReader qurereader = qurecommand.ExecuteReader();
                    while (qurereader.Read())
                    {
                       classdbo.classnum=qurereader.GetInt16("COUNT(*)");
                    }
                connection.Close();
                int ID=0;
                    ID += classdbo.classnum;
                    switch (ClassNumber)
                    {
                        case 1:
                            ID += 1001;
                            break;
                        case 2:
                            ID += 2001;
                            break;
                        case 3:
                            ID += 3001;
                            break;
                        case 4:
                            ID += 4001;
                            break;
                        default:
                            return 0;
                    }
                connection.Open();
                MySqlCommand firstcommand = new MySqlCommand("insert `starplatinum`.`医生表`(`DoctorName`,`ClassNumber`,`DoctorType`,`DoctorSkill`,`ID`) values('"+DoctorName+"',"+ClassNumber+","+DoctorType+",'"+DoctorSkill+"',"+ID+ ");insert `starplatinum`.`account_password`(`Account`,`Password`,`Type`,`CreateTime`,`Message`) values('" + ID + "','" + Password + "','2','" + CreateTime + "','" + Message + "');insert `starplatinum`.`排班表`(`ID`,`1`,`2`,`3`,`4`,`5`,`6`,`7`) values("+ID+","+d1+","+d2+","+d3+","+d4+","+d5+","+d6+","+d7+");", connection);
                    firstcommand.ExecuteNonQuery();
                //**********************         firstcommand        ****************************
                //insert `starplatinum`.`医生表`(`DoctorName`,`ClassNumber`,`DoctorType`,`DoctorSkill`,`ID`) values('"+DoctorName+"',"+ClassNumber+","",'"+DoctorSkill+"',"+ID+");
                //insert `starplatinum`.`account_password`(`Account`,`Password`,`Type`,`CreateTime`,`Message`) values('"+Account+"','"+Password+"','2','"+CreateTime+"','"+Message+"');
                //insert `starplatinum`.`排班表`(`ID`,`1`,`2`,`3`,`4`,`5`,`6`,`7`) values(ID,d1,d2,d3,d4,d5,d6,d7);
                return ID;
                }
            
        }

        public bool UpdateWeekdbo(int id, int d1, int d2, int d3, int d4, int d5, int d6, int d7)
        {
            Weekdbo weekdboclass = new Weekdbo();
            try
            {
                using (MySqlConnection msconnection = GetConnection())
                {
                    msconnection.Open();
                    MySqlCommand mscommand = new MySqlCommand("SELECT * FROM `starplatinum`.`排班表` WHERE `ID` = " + id + ";", msconnection); //读取排班表数据 
                    using (MySqlDataReader reader = mscommand.ExecuteReader())
                    {
                        while (reader.Read())//读取旧排班：周一到周五排班（0：没排班，1：排班）
                        {
                            weekdboclass.monday = reader.GetInt32("1");
                            weekdboclass.tuesday = reader.GetInt32("2");
                            weekdboclass.wednesday = reader.GetInt32("3");
                            weekdboclass.thursday = reader.GetInt32("4");
                            weekdboclass.friday = reader.GetInt32("5");
                            weekdboclass.saturday = reader.GetInt32("6");
                            weekdboclass.sunday = reader.GetInt32("7");
                        }
                    }

                    MySqlCommand registercommand = new MySqlCommand("update `starplatinum`.`排班表` set `1`=" + d1 + ",`2`=" + d2 + ",`3`=" + d3 + ",`4`=" + d4 + ",`5`=" + d5 + ",`6`=" + d6 + ",`7`=" + d7 + " where `ID`=" + id + ";", msconnection);
                    registercommand.ExecuteNonQuery();//更新排班

                    //	如果医生的调班导致已有预约失效，则取消预约，或者调整预约
                    MySqlCommand s1 = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`= 4 where `医生ID`= " + id + " AND `订单状态`=0 AND `预约时间` = '" + DateTime.Today.AddDays(1).ToShortDateString() + "';", msconnection);
                    MySqlCommand s2 = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`= 4 where `医生ID`= " + id + " AND `订单状态`=0 AND`预约时间` = '" + DateTime.Today.AddDays(2).ToShortDateString() + "';", msconnection);
                    MySqlCommand s3 = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`= 4 where `医生ID`= " + id + " AND `订单状态`=0 AND`预约时间` = '" + DateTime.Today.AddDays(3).ToShortDateString() + "';", msconnection);
                    MySqlCommand s4 = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`= 4 where `医生ID`= " + id + " AND `订单状态`=0 AND`预约时间` = '" + DateTime.Today.AddDays(4).ToShortDateString() + "';", msconnection);
                    MySqlCommand s5 = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`= 4 where `医生ID`= " + id + " AND `订单状态`=0 AND`预约时间` = '" + DateTime.Today.AddDays(5).ToShortDateString() + "';", msconnection);
                    MySqlCommand s6 = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`= 4 where `医生ID`= " + id + " AND `订单状态`=0 AND `预约时间` = '" + DateTime.Today.AddDays(6).ToShortDateString() + "';", msconnection);
                    MySqlCommand s7 = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`= 4 where `医生ID`= " + id + " AND `订单状态`=0 AND `预约时间` = '" + DateTime.Today.AddDays(7).ToShortDateString() + "';", msconnection);
//逻辑：如果周3更变，计算今日星期与未来7日内的周三日期差，再对未来的第一个周三那天的数据进行修改
                    if (weekdboclass.monday == 1 && d1 == 0) {
                        switch (Convert.ToInt16(DateTime.Now.DayOfWeek)) {
                            case 1://周一距离下个周一差7天
                                s7.ExecuteNonQuery();
                                break;
                            case 2:
                                s6.ExecuteNonQuery();
                                    break;
                            case 3:
                                s5.ExecuteNonQuery();
                                break;
                            case 4:
                                s4.ExecuteNonQuery();
                                break;
                            case 5:
                                s3.ExecuteNonQuery();
                                break;
                            case 6:
                                s2.ExecuteNonQuery();
                                break;
                            case 0://0代表周日
                                s1.ExecuteNonQuery();
                                break;
                        } }
                    if (weekdboclass.tuesday == 1 && d2 == 0)
                    {
                        switch (Convert.ToInt16(DateTime.Now.DayOfWeek))
                        {
                            case 1://周一距离下个周2差1天
                                s1.ExecuteNonQuery();
                                break;
                            case 2:
                                s7.ExecuteNonQuery();
                                break;
                            case 3:
                                s6.ExecuteNonQuery();
                                break;
                            case 4:
                                s5.ExecuteNonQuery();
                                break;
                            case 5:
                                s4.ExecuteNonQuery();
                                break;
                            case 6:
                                s3.ExecuteNonQuery();
                                break;
                            case 0:
                                s2.ExecuteNonQuery();
                                break;
                        }
                    }
                    if (weekdboclass.wednesday == 1 && d3 == 0)
                    {
                        switch (Convert.ToInt16(DateTime.Now.DayOfWeek))
                        {
                            case 1://周一距下个周3差2天
                                s2.ExecuteNonQuery();
                                break;
                            case 2:
                                s1.ExecuteNonQuery();
                                break;
                            case 3:
                                s7.ExecuteNonQuery();
                                break;
                            case 4:
                                s6.ExecuteNonQuery();
                                break;
                            case 5:
                                s5.ExecuteNonQuery();
                                break;
                            case 6:
                                s4.ExecuteNonQuery();
                                break;
                            case 0:
                                s3.ExecuteNonQuery();
                                break;
                        }
                    }
                    if (weekdboclass.thursday == 1 && d4 == 0)
                    {
                        switch (Convert.ToInt16(DateTime.Now.DayOfWeek))
                        {
                            case 1:
                                s3.ExecuteNonQuery();
                                break;
                            case 2:
                                s2.ExecuteNonQuery();
                                break;
                            case 3:
                                s1.ExecuteNonQuery();
                                break;
                            case 4:
                                s7.ExecuteNonQuery();
                                break;
                            case 5:
                                s6.ExecuteNonQuery();
                                break;
                            case 6:
                                s5.ExecuteNonQuery();
                                break;
                            case 0:
                                s4.ExecuteNonQuery();
                                break;
                        }
                    }
                    if (weekdboclass.friday == 1 && d5 == 0)
                    {
                        switch (Convert.ToInt16(DateTime.Now.DayOfWeek))
                        {
                            case 1:
                                s4.ExecuteNonQuery();
                                break;
                            case 2:
                                s3.ExecuteNonQuery();
                                break;
                            case 3:
                                s2.ExecuteNonQuery();
                                break;
                            case 4:
                                s1.ExecuteNonQuery();
                                break;
                            case 5:
                                s7.ExecuteNonQuery();
                                break;
                            case 6:
                                s6.ExecuteNonQuery();
                                break;
                            case 0:
                                s5.ExecuteNonQuery();
                                break;
                        }
                    }
                    if (weekdboclass.saturday == 1 && d6 == 0)
                    {
                        switch (Convert.ToInt16(DateTime.Now.DayOfWeek))
                        {
                            case 1:
                                s5.ExecuteNonQuery();
                                break;
                            case 2:
                                s4.ExecuteNonQuery();
                                break;
                            case 3:
                                s3.ExecuteNonQuery();
                                break;
                            case 4:
                                s2.ExecuteNonQuery();
                                break;
                            case 5:
                                s1.ExecuteNonQuery();
                                break;
                            case 6:
                                s7.ExecuteNonQuery();
                                break;
                            case 0:
                                s6.ExecuteNonQuery();
                                break;
                        }
                    }
                    if (weekdboclass.sunday == 1 && d7 == 0)
                    {
                        switch (Convert.ToInt16(DateTime.Now.DayOfWeek))
                        {
                            case 1:
                                s6.ExecuteNonQuery();
                                break;
                            case 2:
                                s5.ExecuteNonQuery();
                                break;
                            case 3:
                                s4.ExecuteNonQuery();
                                break;
                            case 4:
                                s3.ExecuteNonQuery();
                                break;
                            case 5:
                                s2.ExecuteNonQuery();
                                break;
                            case 6:
                                s1.ExecuteNonQuery();
                                break;
                            case 0:
                                s7.ExecuteNonQuery();
                                break;
                        }
                    }
                    return true;
                } }
            catch (Exception)
            {
                return false;
            }
        }
        public bool UpdateDoctor(string DoctorName, int ClassNumber, string DoctorSkill, int DoctorType,int id)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand registercommand = new MySqlCommand("update `starplatinum`.`医生表` set `DoctorName`='"+DoctorName+"',`ClassNumber`="+ClassNumber+",`DoctorType`="+DoctorType+",`DoctorSkill`='"+DoctorSkill+"' where `ID`="+id+";", connection);
                    registercommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
        public bool UpdateDoctor2(int id)//	离岗医生信息维护
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand registercommand = new MySqlCommand("update `starplatinum`.`医生表` set `ClassNumber`=0 where `ID`=" + id+ ";update `starplatinum`.`订单表` set `订单状态`= 4 where `医生ID`= " + id + " AND `订单状态`=0 ;", connection);
                    registercommand.ExecuteNonQuery();
                   
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
        public bool UpdateClass(int classnum,string classname,string classplace)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand registercommand = new MySqlCommand("update `starplatinum`.`科室` set `科室`='"+classname+"',`科室位置`='"+classplace+"' where `科室代号`="+classnum+";", connection);
                    registercommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
        public bool WeekdboMessage(int id, int d1, int d2, int d3, int d4, int d5, int d6, int d7)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand registercommand = new MySqlCommand("insert `starplatinum`.`排班请求`(`ID`,`1`,`2`,`3`,`4`,`5`,`6`,`7`) values("+id+","+d1+ ", "+d2+ ", "+d3+ ","+d4+ ", "+d5+ ", "+d6+ ", "+d7+");", connection);
                    registercommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
        public int timeconfirm(string account,string date,int time)//	同一时段，不能安排多个预约就诊
        {
            using (MySqlConnection connection = GetConnection())
            {
                Doctordbo doctordbo = new Doctordbo();
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT COUNT(*) FROM `starplatinum`.`订单表` WHERE `患者账户` ='"+account+"' AND `预约时间`  ='"+date+"' AND `具体时间` ="+time+" AND `订单状态`!=4;", connection);
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    return reader.GetInt16("COUNT(*)");
                }
                return 0;
            }
        }
        public bool fromposting(int id, string date, int time, string name, int age,string account,int kesi)//预约
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand registercommand = new MySqlCommand("insert `starplatinum`.`订单表`(`订单创建时间`,`医生ID`,`预约时间`,`具体时间`,`患者账户`,`患者姓名`,`患者年龄`,`科室代号`) values('" + DateTime.Now.ToString()+ "',"+id+",'"+date+"',"+time+",'"+account+"','"+name+"',"+age+ "," + kesi + ");", connection);
                    registercommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
        public bool frompostingupdate(string date,int time,string createtime)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand registercommand = new MySqlCommand("update `starplatinum`.`订单表` set `预约时间`='" + date + "',`具体时间`="+time+ " where `订单创建时间`='"+createtime+"';", connection);
                    registercommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
        
             public void deletenofreezemessage(string id)
        {
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand registercommand = new MySqlCommand("delete from `starplatinum`.`解冻请求` where `id`= '"+id+"';", connection);
                registercommand.ExecuteNonQuery();
            }
        }
        public bool delete(string account)
        {
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand registercommand = new MySqlCommand("delete from `starplatinum`.`account_password` where `Account`='" + account+"';", connection);
                registercommand.ExecuteNonQuery();
                return true;
            }
        }
        public void DeleteWeekdboMessage(int account)
        {
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand registercommand = new MySqlCommand("delete from `starplatinum`.`排班请求` where `ID`="+account+";", connection);
                registercommand.ExecuteNonQuery();
            }
        }
       
        public bool nofreezemessage(string id)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    connection.Open();
                    MySqlCommand registercommand = new MySqlCommand("insert `starplatinum`.`解冻请求`(`id`) values('"+id+"');", connection);
                    registercommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool mycancel(string createtime)
        {
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand registercommand = new MySqlCommand("update `starplatinum`.`订单表` set `订单状态`=4 where `订单创建时间`='"+createtime+"';", connection);
                registercommand.ExecuteNonQuery();
                return true;
            }
        }

        public void update(string account,string type)
        {
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();           
                MySqlCommand registercommand = new MySqlCommand("update `starplatinum`.`account_password` set `Type`= '"+type+"' where `Account`= '"+account+"';", connection);
                registercommand.ExecuteNonQuery();
            }
        }

        public List<Weekdbo> GetOnesWeekdbo(string id)
        {
            List<Weekdbo> weekdbos = new List<Weekdbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM `starplatinum`.`排班表` WHERE `ID` ="+id+";", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        weekdbos.Add(new Weekdbo()
                        {
                            id = reader.GetInt32("ID"),
                            monday = reader.GetInt32("1"),
                            tuesday = reader.GetInt32("2"),
                            wednesday = reader.GetInt32("3"),
                            thursday = reader.GetInt32("4"),
                            friday = reader.GetInt32("5"),
                            saturday = reader.GetInt32("6"),
                            sunday = reader.GetInt32("7"),
                             hao="1"
                        });
                    }
                }
                MySqlCommand command2 = new MySqlCommand("SELECT * FROM `starplatinum`.`排班请求` WHERE `ID` =" + id + ";", connection);
                using (MySqlDataReader reader2 = command2.ExecuteReader())
                {
                    while (reader2.Read())
                    { //读取数据 
                        weekdbos.Add(new Weekdbo()
                        {
                            id = reader2.GetInt32("ID"),
                            monday = reader2.GetInt32("1"),
                            tuesday = reader2.GetInt32("2"),
                            wednesday = reader2.GetInt32("3"),
                            thursday = reader2.GetInt32("4"),
                            friday = reader2.GetInt32("5"),
                            saturday = reader2.GetInt32("6"),
                            sunday = reader2.GetInt32("7"),
                            hao="2"
                        });
                    }
                }
            }
            return weekdbos;
        }
  
public List<Weekdbo> GetAllWeekdbo()
        {
            List<Weekdbo>weekdbos = new List<Weekdbo>();
            using (MySqlConnection connection = GetConnection())
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT   `排班表`.`*`,`医生表`.`DoctorName`,`医生表`.`ClassNumber` FROM `排班表`,`医生表` WHERE  `排班表`.`ID`=`医生表`.`ID` ORDER BY `ID` ASC  LIMIT 0,50;", connection);//DESC:逆序，ASC：正序
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { //读取数据 
                        weekdbos.Add(new Weekdbo()
                        {
                            id = reader.GetInt32("ID"),
                            name = reader.GetString("DoctorName"),
                            classnum = reader.GetInt32("ClassNumber"),
                            monday = reader.GetInt32("1"),
                            tuesday = reader.GetInt32("2"),
                            wednesday = reader.GetInt32("3"),
                            thursday = reader.GetInt32("4"),
                            friday = reader.GetInt32("5"),
                            saturday = reader.GetInt32("6"),
                            sunday = reader.GetInt32("7")
                        });
                    }
                }

            }
            return weekdbos;
        }
    }
}